--SELECT * FROM FN_GET_BASE_PRODUCT_PROCESS_FLOW ( 1, 1, 2006 ) ORDER BY PACKAGE_ID_ORDER, PATH_NUMBER, PS_TYPE_NAME_ORDER DESC
ALTER FUNCTION FN_GET_BASE_PRODUCT_PROCESS_FLOW ( @BASE_PRODUCT_ID INT, @CURRENT_QUARTER INT, @CURRENT_YEAR INT )
RETURNS @FN_RESULTS TABLE ( PATH_NUMBER INT,
				PROCESS_STEP_ID INT, 
				PS_TYPE VARCHAR (50), 
				PS_TYPE_NAME VARCHAR(50), 
				PRODUCT_NAME VARCHAR (50),
				PACKAGE_ID_ORDER INT DEFAULT 0,
				IS_LEAVE_SPACE INT DEFAULT 1,
				PACKAGE_ID VARCHAR(200),
				CYCLE_TIME_NORMAL INT,
				YIELD FLOAT,
				PS_TYPE_NAME_ORDER INT,
				LOCATION_ID INT )
AS
BEGIN
	DECLARE @CLASS_STORE_CURSOR CURSOR
	DECLARE @PATH_CURSOR CURSOR
	DECLARE @PROCESS_STEP_ID INT
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @PRODUCT_NAME VARCHAR(50)
	DECLARE @PATH_NUMBER INT
	DECLARE @TEMP_PATH_NUMBER INT
	DECLARE @PACKAGE_ID_ORDER INT
	SET @PATH_NUMBER = 0
	DECLARE @PACKAGE_TYPE VARCHAR(200)
	DECLARE @TEMP_PATH_TABLE TABLE ( PATH_NUMBER INT, PROCESS_STEP_ID INT, PS_TYPE VARCHAR(50), PS_TYPE_NAME VARCHAR(50), PRODUCT_NAME VARCHAR(50), PS_TYPE_NAME_ORDER INT )

	--SET @CLASS_STORE_CURSOR = CURSOR FOR SELECT P.ProcessStep_ID FROM PRODUCT P, Process_Step PS WHERE PS_TYPE_NAME = 'Class Store' AND PS.ProcessStep_ID = P.ProcessStep_ID AND P_Parent_Product_ID = @BASE_PRODUCT_ID
	SET @CLASS_STORE_CURSOR = CURSOR FOR SELECT P.PROCESSSTEP_ID FROM PRODUCT P, Process_Step PS WHERE PS.PS_TYPE_NAME = 'Finished Goods' AND P.P_TYPE IS NULL AND PS.ProcessStep_ID = P.ProcessStep_ID AND P.P_Parent_Product_ID = @BASE_PRODUCT_ID AND P.IS_PRODUCT_SETUP_FG = 0
	OPEN @CLASS_STORE_CURSOR
	FETCH NEXT FROM @CLASS_STORE_CURSOR INTO @PROCESS_STEP_ID

	WHILE @@FETCH_STATUS = 0
	 BEGIN
		INSERT INTO @TEMP_PATH_TABLE SELECT PATH_NUMBER, PROCESSSTEP_ID, PS_Type, PS_Type_Name, Product_Name, Row_Id FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @PROCESS_STEP_ID, 0 ) ORDER BY PATH_NUMBER, Row_Id DESC
		SET @PATH_CURSOR = CURSOR FOR SELECT DISTINCT PATH_NUMBER FROM @TEMP_PATH_TABLE
		OPEN @PATH_CURSOR
		FETCH NEXT FROM @PATH_CURSOR INTO @TEMP_PATH_NUMBER
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			SET @PATH_NUMBER = @PATH_NUMBER + 1
			INSERT INTO @FN_RESULTS(PATH_NUMBER, PROCESS_STEP_ID, PS_TYPE, PS_TYPE_NAME, PRODUCT_NAME, PS_TYPE_NAME_ORDER ) SELECT @PATH_NUMBER, PROCESS_STEP_ID, PS_TYPE, PS_TYPE_NAME, PRODUCT_NAME, PS_TYPE_NAME_ORDER FROM @TEMP_PATH_TABLE WHERE PATH_NUMBER = @TEMP_PATH_NUMBER
			SELECT @PACKAGE_TYPE = Parameter_Value FROM Process_Parameter WHERE Parameter_Key = 'Package ID' AND ProcessStep_ID = (SELECT PROCESS_STEP_ID FROM @FN_RESULTS WHERE PATH_NUMBER = @PATH_NUMBER AND PS_TYPE_NAME = 'Assembly' )
			UPDATE @FN_RESULTS SET PACKAGE_ID = @PACKAGE_TYPE WHERE PATH_NUMBER = @PATH_NUMBER AND (PS_TYPE_NAME = 'Assembly' OR PS_TYPE_NAME = 'TEST' OR PS_TYPE_NAME = 'Class Store' OR PS_TYPE_NAME = 'Finished Goods' OR PS_TYPE_NAME = 'Mark & Pack' OR PS_TYPE_NAME = 'Mark &amp; Pack')
			UPDATE @FN_RESULTS SET PACKAGE_ID_ORDER = CASE  WHEN @PACKAGE_TYPE = 'TSOP 32 (Pb Free)' THEN 1
									WHEN @PACKAGE_TYPE = 'TSOP 32 (with Pb)' THEN 2
									WHEN @PACKAGE_TYPE = 'MMC-1' THEN 3
									WHEN @PACKAGE_TYPE = 'MMC-2' THEN 4
									WHEN @PACKAGE_TYPE = 'TQFP 80' THEN 5
									ELSE 6 END WHERE PATH_NUMBER = @PATH_NUMBER
			SET @PACKAGE_TYPE = NULL
			SET @TEMP_PATH_NUMBER = NULL
			FETCH NEXT FROM @PATH_CURSOR INTO @TEMP_PATH_NUMBER
		 END
		CLOSE @PATH_CURSOR
		DEALLOCATE @PATH_CURSOR
		DELETE FROM @TEMP_PATH_TABLE
		SET @PROCESS_STEP_ID = NULL
		FETCH NEXT FROM @CLASS_STORE_CURSOR INTO @PROCESS_STEP_ID
	 END
	CLOSE @CLASS_STORE_CURSOR
	DEALLOCATE @CLASS_STORE_CURSOR

	---
	DECLARE @PROCESS_STEPS_CURSOR CURSOR
	DECLARE @YIELD FLOAT
	DECLARE @CYCLE_TIME_NORMAL INT
	DECLARE @LOCATION_ID INT
	SET @PROCESS_STEPS_CURSOR = CURSOR FOR SELECT DISTINCT PROCESS_STEP_ID FROM @FN_RESULTS
	OPEN @PROCESS_STEPS_CURSOR
	FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESS_STEP_ID
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		/*
		   CONSIDERING CYCLE_TIME_NORMAL FOR HIGH PRIORITY.
		   PRIORITY-> 1 is HIGH
		   PRIORITY-> 0 is No PRIORITY ( default least )
		   eg:-  1,2,3,4...0 ( high --> low )
		*/
		SELECT TOP 1 @CYCLE_TIME_NORMAL = PSLT_Cycle_Time_Normal, @LOCATION_ID = Location_ID FROM Process_Step_Location_Table WHERE ProcessStep_ID = @PROCESS_STEP_ID AND PSLT_PRIORITY != 0 ORDER BY PSLT_PRIORITY ASC
		IF ( @CYCLE_TIME_NORMAL IS NULL )
		 BEGIN
			SELECT TOP 1 @CYCLE_TIME_NORMAL = PSLT_Cycle_Time_Normal, @LOCATION_ID = Location_ID FROM Process_Step_Location_Table WHERE ProcessStep_ID = @PROCESS_STEP_ID
		 END
		SELECT @YIELD = CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_YIELD
				 	WHEN @CURRENT_QUARTER = 2 THEN Q2_YIELD
					WHEN @CURRENT_QUARTER = 3 THEN Q3_YIELD
					WHEN @CURRENT_QUARTER = 4 THEN Q4_YIELD END FROM Update_View_Cost_Table WHERE ProcessStep_id = @PROCESS_STEP_ID AND Location_id = @LOCATION_ID AND YEAR(Fin_Year) = @CURRENT_YEAR
		UPDATE @FN_RESULTS SET CYCLE_TIME_NORMAL = ISNULL(@CYCLE_TIME_NORMAL,0), YIELD = ISNULL(@YIELD,0), LOCATION_ID = @LOCATION_ID WHERE PROCESS_STEP_ID = @PROCESS_STEP_ID
		SET @PROCESS_STEP_ID = NULL
		SET @YIELD = 0
		SET @CYCLE_TIME_NORMAL = NULL
		FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESS_STEP_ID
	 END
	
	---	


	DECLARE @PACKAGE_ID_ORDER_CURSOR CURSOR
	DECLARE @PREV_PACKAGE_ID_ORDER INT
	SET @PACKAGE_ID_ORDER_CURSOR = CURSOR FOR SELECT DISTINCT PACKAGE_ID_ORDER FROM @FN_RESULTS ORDER BY PACKAGE_ID_ORDER

	OPEN @PACKAGE_ID_ORDER_CURSOR
	FETCH NEXT FROM @PACKAGE_ID_ORDER_CURSOR INTO @PACKAGE_ID_ORDER
	SET @PREV_PACKAGE_ID_ORDER = @PACKAGE_ID_ORDER
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		IF ( @PREV_PACKAGE_ID_ORDER != @PACKAGE_ID_ORDER )
		 BEGIN
			UPDATE @FN_RESULTS SET IS_LEAVE_SPACE = 0 WHERE PACKAGE_ID_ORDER = @PACKAGE_ID_ORDER AND PATH_NUMBER = ( SELECT MIN(PATH_NUMBER) FROM @FN_RESULTS WHERE PACKAGE_ID_ORDER = @PACKAGE_ID_ORDER )
			SET @PREV_PACKAGE_ID_ORDER = @PACKAGE_ID_ORDER
		 END
		SET @PACKAGE_ID_ORDER = NULL
		FETCH NEXT FROM @PACKAGE_ID_ORDER_CURSOR INTO @PACKAGE_ID_ORDER
	 END

RETURN

END